package com.ruoyi.tongji.run;

import com.ruoyi.common.utils.SpringUtil;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.plasma.controller.PlasmaFollowUpController;
import com.ruoyi.plasma.domain.PlasmaFollowUp;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.io.File;
import java.io.IOException;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.util.*;

public class 智能客服回访名单 {



    public static String root = "E:\\文档\\2024\\回访记录\\智能客服回访\\";

    public static void main(String[] args) {
        List list = new ArrayList();

        //list.addAll(createXinHuifangList());
        LocalDate date = LocalDate.now();
        DayOfWeek dayOfWeek = date.getDayOfWeek();
        if (StringUtils.equals(dayOfWeek.name(),"SUNDAY") == false) {
            list.addAll(create19tianList());
        }

        list.addAll(create30tianList());
        list.addAll(create60tianList());
        list.addAll(create90tianList());
        list.addAll(create180tianList());
        list.addAll(create365tianList());

        //ApplicationContext ac =new ClassPathXmlApplicationContext("beans.xml");
        HashMap data = new HashMap();
        data.put("donorList",list);
        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//总(" + list.size() + ")-" + ymd + ".xlsx",data);

        create14tianCannotRegisterList();
    }

    /**
     * 创建回访文件，不排除24次的
     * @param start 需要是负数，-1代表查询日期的前一天，-19代表查询日期的前19天
     * @param end
     * @return
     */
    public static List<HashMap> createFollowUpListUnexclude24Times(int start, int end) {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数\n," +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL " + start + " DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL " + end + " DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 and a.次数 < 24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        return list;
    }

    public static List<HashMap> createFollowUpListExclude24Times(int start, int end) {
        LocalDate date = LocalDate.now();
        DayOfWeek dayOfWeek = date.getDayOfWeek();
        if (start == -19 && end == -19 && StringUtils.equals(dayOfWeek.name(),"SUNDAY") == true) {
            return new ArrayList<>();
        }
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL " + start + " DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL " + end + " DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);

        data.put("donorList",list);

        if (list.size() == 0) return list;

        return list;
    }

    public static List<HashMap> create90tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();


        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -90 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -90 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//90天（" + list.size() + "）.xlsx",data);

        return list;
    }
    public static List<HashMap> create60tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//60天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create180tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期,donor.remark\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -180 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -180 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//180天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create365tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期,donor.remark\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -365 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -365 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//365天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> createZidingyitianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期,donor.remark\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -551 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -186 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//365天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create19tianList() {
        System.out.println("开始19天查询");
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -19 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -19 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);

        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//19天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> createXinHuifangList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from t_bims_donor as d where d.firstCollectDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d2300000')",
                new Object[]{}), HashMap.class);

        //如果间隔13天的新浆员是0，可能是周天时，第十四天时周一，再按照间隔12天查
        if (list.size() == 0) {
            list = taibangSqlManager.execute(new SQLReady("select * from t_bims_donor as d where d.firstCollectDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -12 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -12 DAY),'%Y%m%d2300000')",
                    new Object[]{}), HashMap.class);
        }
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//新浆员回访（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create30tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -30 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -30 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//30天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create13tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 13 DAY)) as 13天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//13天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create14tianCannotRegisterList() {
        System.out.println("开始14天不能来的查询");
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,donor.address,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数=24 and a.下次可献浆日期 > a.14天到期日期",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//14天（24次提醒）（" + list.size() + "）.xlsx",data);

        return list;
    }
}
